﻿<%@ Page Language="C#" AutoEventWireup="true" CodeFile="searchForStudent.aspx.cs" Inherits="_104_searchForStudent" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">


        .style1
        {
            font-family: 微軟正黑體;
            font-size: xx-large;
        }
        .style2
        {
            text-align: center;
        }
        .style3
        {
            width: 79%;
        }
        .style4
        {
            height: 23px;
            color: #FFFF00;
            background-color: #000000;
        }
        .style5
        {
            color: #66FF66;
            background-color: #000000;
        }
        .style6
        {
            color: #FF0000;
            background-color: #000000;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div class="style2">
    
        <span class="style1">企業選才</span><br />
        <br />
        <table align="center" class="style3">
            <tr>
                <td colspan="2">
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    學年平均成績須達：</td>
                <td>
                    <asp:RadioButtonList ID="RadioButtonList1" runat="server" 
                        RepeatDirection="Horizontal">
                        <asp:ListItem>60</asp:ListItem>
                        <asp:ListItem>70</asp:ListItem>
                        <asp:ListItem>80</asp:ListItem>
                        <asp:ListItem>90</asp:ListItem>
                        <asp:ListItem Value="0">不限</asp:ListItem>
                    </asp:RadioButtonList>
                </td>
            </tr>
            <tr>
                <td>
                    院別：</td>
                <td>
                    <asp:RadioButtonList ID="RadioButtonList2" runat="server" 
                        DataSourceID="SqlDataSource1" DataTextField="DepartmentName" 
                        DataValueField="DepartmentId" RepeatDirection="Horizontal">
                    </asp:RadioButtonList>
                </td>
            </tr>
            <tr>
                <td>
                    包含技能：</td>
                <td>
                    <asp:RadioButtonList ID="RadioButtonList3" runat="server" 
                        DataSourceID="SqlDataSource2" DataTextField="SkillName" 
                        DataValueField="SkillId">
                    </asp:RadioButtonList>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="查詢" />
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    名單：(選取已顯示詳細資料)</td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                        BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" 
                        CellPadding="4" DataKeyNames="StudentId" DataSourceID="SqlDataSource4" 
                        Width="498px">
                        <Columns>
                            <asp:BoundField DataField="StudentId" HeaderText="StudentId" ReadOnly="True" 
                                SortExpression="StudentId" />
                            <asp:BoundField DataField="StudentName" HeaderText="StudentName" 
                                SortExpression="StudentName" />
                            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
                            <asp:BoundField DataField="Gender" HeaderText="Gender" 
                                SortExpression="Gender" />
                            <asp:BoundField DataField="Expr1" HeaderText="Expr1" ReadOnly="True" 
                                SortExpression="Expr1" />
                            <asp:CommandField ButtonType="Button" ShowSelectButton="True" />
                        </Columns>
                        <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
                        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
                        <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
                        <RowStyle BackColor="White" ForeColor="#330099" />
                        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
                        <SortedAscendingCellStyle BackColor="#FEFCEB" />
                        <SortedAscendingHeaderStyle BackColor="#AF0101" />
                        <SortedDescendingCellStyle BackColor="#F6F0C0" />
                        <SortedDescendingHeaderStyle BackColor="#7E0000" />
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td class="style4">
                    個人詳細資料：</td>
                <td class="style4">
                    技能資料：</td>
            </tr>
            <tr>
                <td>
                    <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" 
                        DataKeyNames="StudentId" DataSourceID="SqlDataSource5" Height="50px" 
                        Width="431px" CellPadding="4" ForeColor="#333333" GridLines="None" 
                        style="margin-right: 1px">
                        <AlternatingRowStyle BackColor="White" />
                        <CommandRowStyle BackColor="#D1DDF1" Font-Bold="True" />
                        <EditRowStyle BackColor="#2461BF" />
                        <FieldHeaderStyle BackColor="#DEE8F5" Font-Bold="True" />
                        <Fields>
                            <asp:BoundField DataField="StudentName" HeaderText="姓名" 
                                SortExpression="StudentName" />
                            <asp:BoundField DataField="Gender" HeaderText="性別" 
                                SortExpression="Gender" />
                            <asp:BoundField DataField="Email" HeaderText="電子郵件" SortExpression="Email" />
                            <asp:TemplateField HeaderText="自我介紹" SortExpression="SelfIntro">
                                <EditItemTemplate>
                                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("SelfIntro") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <InsertItemTemplate>
                                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("SelfIntro") %>'></asp:TextBox>
                                </InsertItemTemplate>
                                <ItemTemplate>
                                    <asp:TextBox ID="TextBox2" runat="server" Height="103px" ReadOnly="True" 
                                        Text='<%# Bind("SelfIntro", "{0}") %>' TextMode="MultiLine" Width="230px"></asp:TextBox>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField DataField="DepartmentName" HeaderText="系別" 
                                SortExpression="DepartmentName" />
                        </Fields>
                        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                        <RowStyle BackColor="#EFF3FB" />
                    </asp:DetailsView>
                </td>
                <td>
                    <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" 
                        DataKeyNames="serial" DataSourceID="SqlDataSource6" BackColor="White" 
                        BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4">
                        <Columns>
                            <asp:BoundField DataField="SkillName" HeaderText="技能名稱" 
                                SortExpression="SkillName" />
                            <asp:BoundField DataField="Intro" HeaderText="介紹" SortExpression="Intro" />
                        </Columns>
                        <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
                        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
                        <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
                        <RowStyle BackColor="White" ForeColor="#330099" />
                        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
                        <SortedAscendingCellStyle BackColor="#FEFCEB" />
                        <SortedAscendingHeaderStyle BackColor="#AF0101" />
                        <SortedDescendingCellStyle BackColor="#F6F0C0" />
                        <SortedDescendingHeaderStyle BackColor="#7E0000" />
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td colspan="2" class="style5">
                    曾修習課程：</td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:GridView ID="GridView3" runat="server" AutoGenerateColumns="False" 
                        DataKeyNames="serial" DataSourceID="SqlDataSource7" CellPadding="4" 
                        ForeColor="#333333" GridLines="None" Width="386px">
                        <AlternatingRowStyle BackColor="White" />
                        <Columns>
                            <asp:BoundField DataField="CourseName" HeaderText="課程名稱" 
                                SortExpression="CourseName" />
                            <asp:BoundField DataField="Grade" HeaderText="成績" SortExpression="Grade" />
                        </Columns>
                        <EditRowStyle BackColor="#2461BF" />
                        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                        <RowStyle BackColor="#EFF3FB" />
                        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                        <SortedAscendingCellStyle BackColor="#F5F7FB" />
                        <SortedAscendingHeaderStyle BackColor="#6D95E1" />
                        <SortedDescendingCellStyle BackColor="#E9EBEF" />
                        <SortedDescendingHeaderStyle BackColor="#4870BE" />
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td colspan="2" class="style6">
                    曾參與活動：</td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:GridView ID="GridView4" runat="server" AutoGenerateColumns="False" 
                        DataKeyNames="serial" DataSourceID="SqlDataSource8" CellPadding="4" 
                        ForeColor="#333333" GridLines="None" Width="388px">
                        <AlternatingRowStyle BackColor="White" />
                        <Columns>
                            <asp:BoundField DataField="ArtivityName" HeaderText="活動名稱" 
                                SortExpression="ArtivityName" />
                            <asp:BoundField DataField="Intro" HeaderText="介紹" SortExpression="Intro" />
                            <asp:BoundField DataField="Time" DataFormatString="{0:d}" HeaderText="參與時間" 
                                SortExpression="Time" />
                        </Columns>
                        <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                        <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                        <SortedAscendingCellStyle BackColor="#FDF5AC" />
                        <SortedAscendingHeaderStyle BackColor="#4D0000" />
                        <SortedDescendingCellStyle BackColor="#FCF6C0" />
                        <SortedDescendingHeaderStyle BackColor="#820000" />
                    </asp:GridView>
                </td>
            </tr>
            </table>
        <br />
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ProjectConnectionString %>" 
            SelectCommand="SELECT * FROM [Department]"></asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ProjectConnectionString %>" 
            SelectCommand="SELECT * FROM [Skills]"></asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource3" runat="server"></asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource4" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ProjectConnectionString %>" 
            SelectCommand="SELECT Student.StudentId, Student.StudentName, Student.Birth, Student.Email, Student.Gender, Student.SelfIntro, Student.Address, Student.SSN, Student.DepartmentId, Has_Ski.SkillId, AVG(CourseTaken.Grade) AS Expr1 FROM Student INNER JOIN Department ON Student.DepartmentId = Department.DepartmentId INNER JOIN Has_Ski ON Student.StudentId = Has_Ski.StudentId INNER JOIN Skills ON Has_Ski.SkillId = Skills.SkillId INNER JOIN CourseTaken ON Student.StudentId = CourseTaken.StudentId GROUP BY Student.StudentId, Student.StudentName, Student.Birth, Student.Email, Student.Gender, Student.SelfIntro, Student.Address, Student.SSN, Student.DepartmentId, Has_Ski.SkillId HAVING (Has_Ski.SkillId = @SkillId) AND (Student.DepartmentId = @DepartmentId) AND (AVG(CourseTaken.Grade) &gt;= @Expr1)">
            <SelectParameters>
                <asp:ControlParameter ControlID="RadioButtonList3" Name="SkillId" 
                    PropertyName="SelectedValue" />
                <asp:ControlParameter ControlID="RadioButtonList2" Name="DepartmentId" 
                    PropertyName="SelectedValue" />
                <asp:ControlParameter ControlID="RadioButtonList1" Name="Expr1" 
                    PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource5" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ProjectConnectionString %>" 
            SelectCommand="SELECT Student.StudentId, Student.StudentName, Student.Email, Student.Gender, Student.SelfIntro, Department.DepartmentName FROM Student INNER JOIN Department ON Student.DepartmentId = Department.DepartmentId WHERE (Student.StudentId = @sid)">
            <SelectParameters>
                <asp:ControlParameter ControlID="GridView1" Name="sid" 
                    PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource6" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ProjectConnectionString %>" 
            
            
            SelectCommand="SELECT Has_Ski.StudentId, Has_Ski.SkillId, Has_Ski.serial, Skills.SkillName, Skills.Intro FROM Has_Ski INNER JOIN Skills ON Has_Ski.SkillId = Skills.SkillId WHERE (Has_Ski.StudentId = @StudentId) AND (Has_Ski.SkillId &lt;&gt; @id)">
            <SelectParameters>
                <asp:ControlParameter ControlID="GridView1" Name="StudentId" 
                    PropertyName="SelectedValue" Type="Int32" />
                <asp:Parameter DefaultValue="3" Name="id" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource7" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ProjectConnectionString %>" 
            SelectCommand="SELECT CourseTaken.StudentId, CourseTaken.CourseId, CourseTaken.Grade, CourseTaken.serial, Course.CourseName FROM CourseTaken INNER JOIN Course ON CourseTaken.CourseId = Course.CourseId WHERE (CourseTaken.StudentId = @StudentId)">
            <SelectParameters>
                <asp:ControlParameter ControlID="GridView1" Name="StudentId" 
                    PropertyName="SelectedValue" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource8" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ProjectConnectionString %>" 
            SelectCommand="SELECT * FROM [Activity] WHERE ([StudentId] = @StudentId)">
            <SelectParameters>
                <asp:ControlParameter ControlID="GridView1" Name="StudentId" 
                    PropertyName="SelectedValue" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>
        <br />
    
    </div>
    </form>
</body>
</html>
